const connection = require('../../sql');

class MaterialService {
  // 获取分类路径
  async getCategoryPath(category_id) {
    try {
      const sql = `
        WITH RECURSIVE category_path AS (
          SELECT id, parent_id, category_name
          FROM basic_material_type
          WHERE id = ?
          UNION ALL
          SELECT t.id, t.parent_id, t.category_name
          FROM basic_material_type t
          INNER JOIN category_path cp ON t.id = cp.parent_id
        )
        SELECT category_name
        FROM category_path
        ORDER BY parent_id ASC
      `;
      const [rows] = await connection.execute(sql, [category_id]);

      if (rows.length === 0) {
        throw new Error('分类不存在');
      }
      return rows.map(row => row.category_name).join('-');
    } catch (error) {
      throw error;
    }
  }

  // 验证分类有效性
  async validateCategory(category_id) {
    const [rows] = await connection.execute('SELECT id FROM basic_material_type WHERE id = ? AND status = 1017001', [
      category_id
    ]);
    if (rows.length === 0) {
      throw new Error('分类不存在或未启用');
    }
  }

  // 验证编码唯一性
  async validateCode(code, excludeId = null) {
    if (!code) return;
    const sql = excludeId
      ? 'SELECT id FROM basic_material WHERE code = ? AND id != ?'
      : 'SELECT id FROM basic_material WHERE code = ?';
    const params = excludeId ? [code, excludeId] : [code];
    const [rows] = await connection.execute(sql, params);
    if (rows.length > 0) {
      throw new Error('物料编码已存在');
    }
  }

  async createMaterial({
    code,
    partNum,
    name,
    state,
    category_id,
    spec,
    unit,
    brand,
    safety_stock,
    remark,
    project,
    shader,
    thick,
    width,
    lengths,
    stride,
    weight,
    consumption,
    outNum,
    create_id,
    updata_id,
    category_name,
    type,
    url
  }) {
    try {
      // 验证分类和编码
      // await this.validateCategory(category_id);
      await this.validateCode(code);
      // const category_name = await this.getCategoryPath(category_id);

      const now = new Date();
      const sql = `
        INSERT INTO basic_material (
          code, partNum, name, state, category_id, category_name,
          spec, unit, brand, safety_stock, remark, project,
          shader, thick, width, lengths, stride, weight,
          consumption, outNum, create_id, updata_id, create_at, updata_at,type,url
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      const [result] = await connection.execute(sql, [
        code || null,
        partNum || null,
        name,
        state,
        category_id || null,
        category_name || null,
        spec || null,
        unit !== undefined ? unit : null,
        brand || null,
        safety_stock !== undefined ? safety_stock : null,
        remark || null,
        project || null,
        shader || null,
        thick || null,
        width || null,
        lengths || null,
        stride || null,
        weight || null,
        consumption || null,
        outNum || null,
        create_id,
        updata_id,
        now,
        now,
        type,
        url
      ]);
      return { id: result.insertId };
    } catch (error) {
      throw error;
    }
  }

  async deleteMaterials(ids) {
    try {
      const sql = `
        DELETE FROM basic_material
        WHERE id IN (${ids.map(() => '?').join(',')})
      `;
      const [result] = await connection.execute(sql, ids);
      if (result.affectedRows === 0) {
        throw new Error('没有可删除的物料');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async updateMaterial({
    id,
    code,
    partNum,
    name,
    state,
    category_id,
    spec,
    unit,
    brand,
    safety_stock,
    remark,
    project,
    shader,
    thick,
    width,
    lengths,
    stride,
    weight,
    consumption,
    outNum,
    updata_id,
    category_name,
    url
  }) {
    try {
      // await this.validateCategory(category_id);
      await this.validateCode(code, id);
      // const category_name = await this.getCategoryPath(category_id);

      const sql = `
        UPDATE basic_material
        SET
          code = ?, partNum = ?, name = ?, state = ?,
          category_id = ?, category_name = ?, spec = ?,
          unit = ?, brand = ?, safety_stock = ?, remark = ?,
          project = ?, shader = ?, thick = ?, width = ?,
          lengths = ?, stride = ?, weight = ?, consumption = ?,
          outNum = ?, updata_id = ?, updata_at = ?,url = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [
        code || null,
        partNum || null,
        name,
        state !== undefined ? state : null,
        category_id || null,
        category_name || null,
        spec || null,
        unit !== undefined ? unit : null,
        brand || null,
        safety_stock !== undefined ? safety_stock : null,
        remark || null,
        project || null,
        shader || null,
        thick || null,
        width || null,
        lengths || null,
        stride || null,
        weight || null,
        consumption || null,
        outNum || null,
        updata_id,
        now,
        url,
        id
      ]);
      if (result.affectedRows === 0) {
        throw new Error('物料不存在');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async getMaterialList({ page, limit, code, partNum, name, category_id, project, shader, brand, state, type }) {
    const offset = (page - 1) * limit;
    let whereClause = '';
    const params = [];
    let categoryIds = [];
    if (category_id && category_id.length > 0) {
      // Query all descendant category IDs
      const sqlCategories = `
        WITH RECURSIVE category_tree AS (
          SELECT id
          FROM basic_material_type
          WHERE id IN (${category_id.map(() => '?').join(',')})
          UNION ALL
          SELECT t.id
          FROM basic_material_type t
          INNER JOIN category_tree ct ON t.parent_id = ct.id
        )
        SELECT id FROM category_tree
      `;
      const [categoryRows] = await connection.execute(sqlCategories, category_id);
      categoryIds = categoryRows.map(row => row.id);
      if (categoryIds.length === 0) {
        return {
          current: page,
          limit,
          totalPage: 1,
          total: 0,
          rows: []
        };
      }
      whereClause += ` AND m.category_id IN (${categoryIds.map(() => '?').join(',')})`;
      params.push(...categoryIds);
    }

    if (code) {
      whereClause += ' AND m.code LIKE ?';
      params.push(`%${code}%`);
    }
    if (partNum) {
      whereClause += ' AND m.partNum LIKE ?';
      params.push(`%${partNum}%`);
    }
    if (name) {
      whereClause += ' AND m.name LIKE ?';
      params.push(`%${name}%`);
    }
    if (project) {
      whereClause += ' AND m.project LIKE ?';
      params.push(`%${project}%`);
    }
    if (shader) {
      whereClause += ' AND m.shader LIKE ?';
      params.push(`%${shader}%`);
    }
    if (brand) {
      whereClause += ' AND m.brand LIKE ?';
      params.push(`%${brand}%`);
    }
    if (state) {
      whereClause += ' AND m.state = ?';
      params.push(state);
    }

    const countSql = `
      SELECT COUNT(*) as count
      FROM basic_material m
      WHERE 1=1 AND type = ${type} ${whereClause}
    `;
    const listSql = `
      SELECT 
        m.id, m.code, m.partNum, m.name, m.state,
        m.category_id, m.category_name, m.spec, m.unit,
        m.brand, m.safety_stock, m.remark, m.project,
        m.shader, m.thick, m.width, m.lengths, m.stride,
        m.weight, m.consumption, m.outNum, m.create_id,
        m.create_at, m.updata_id, m.updata_at,m.url,
        creator.username AS create_username,
        updater.username AS updata_username
      FROM basic_material m
      LEFT JOIN sys_user creator ON m.create_id = creator.id AND creator.del_flag = 'F'
      LEFT JOIN sys_user updater ON m.updata_id = updater.id AND updater.del_flag = 'F'
      WHERE 1=1 AND type = ${type} ${whereClause}
      ORDER BY m.create_at DESC
      LIMIT ? OFFSET ?
    `;

    try {
      const [countResult] = await connection.execute(countSql, params);
      const [rows] = await connection.execute(listSql, [...params, String(limit), String(offset)]);

      return {
        current: page,
        limit,
        totalPage: Math.ceil(countResult[0].count / limit) || 1,
        total: countResult[0].count,
        rows
      };
    } catch (error) {
      throw error;
    }
  }

  async updateMaterialState({ id, newState, updata_id }) {
    try {
      const sql = `
        UPDATE basic_material
        SET state = ?, updata_id = ?, updata_at = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [newState, updata_id, now, id]);
      if (result.affectedRows === 0) {
        throw new Error('没有可更新的物料');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async getMaterialInfo(id) {
    const sql = `
      SELECT 
        m.id, m.code, m.partNum, m.name, m.state,
        m.category_id, m.category_name, m.spec, m.unit,
        m.brand, m.safety_stock, m.remark, m.project,
        m.shader, m.thick, m.width, m.lengths, m.stride,
        m.weight, m.consumption, m.outNum, m.create_id,
        m.create_at, m.updata_id, m.updata_at,m.url,
        creator.username AS create_username,
        updater.username AS updata_username
      FROM basic_material m
      LEFT JOIN sys_user creator ON m.create_id = creator.id AND creator.del_flag = 'F'
      LEFT JOIN sys_user updater ON m.updata_id = updater.id AND updater.del_flag = 'F'
      WHERE m.id = ?
    `;

    try {
      const [rows] = await connection.execute(sql, [id]);

      if (rows.length === 0) {
        throw new Error('物料不存在');
      }
      return rows[0];
    } catch (error) {
      throw error;
    }
  }
}

module.exports = new MaterialService();
